﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-04-01
修改:
修改时间: 
功能: 小品类深度
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using System.Data;
using com.nm.library.Helper;
using com.nm.library;

namespace PlanAPI
{
    [Description("小品类深度")]
    public class PLShopMSaleTrendHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        //const string TUIPIN = "000005";

        /// Writer：梁家健
        /// Create Date：2017-04-01
        [MustLogin]
        [Description("小品类深度列表")]
        [ReturnType(typeof(PaperData<IGrouping<string,PLShopMSaleTrendModel.ListModel>>))]
        public ApiResult GetPLShopMSaleTrendList([ParamDescription("商品区域")] string ParentAreaID = "", 
            [ParamDescription("类别")]string KindID="", [ParamDescription("系列")]string SeriesID = "", 
            [ParamDescription("商品层")]string MaterialProperty="", [ParamDescription("品类")]string ModelID = "",
            [ParamDescription("店铺")] string ShopIDs = "", [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (set == null) return Error("没有要修改企划的月份");
            var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
            var count = ReturnPLShopMSaleTrendCount(set.Yearv.Value, set.Monthv.Value ,ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, shopList);
            var dt = ReturnPLShopMSaleTrendList(set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, shopList, Page, Size);
            var result = PaperData<IGrouping<string, PLShopMSaleTrendModel.ListModel>>.Create(count, Size, Page);
            var list = ModelConvertHelper<PLShopMSaleTrendModel.ListModel>.ConvertToModel(dt);
            result.Items =
                list.GroupBy(
                    d =>
                        d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" + d.KindID + "_" + d.MaterialProperty + "_" +
                        d.ModelID + "_" + d.SeriesID)
                        .ToList();
            return Success(result);
        }

        /// <summary>
        /// 返回小品类深度
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="Keys"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-04-01
        public DataTable ReturnPLShopMSaleTrendList(long Yearv, long Monthv, string ParentAreaID, string KindID,
            string SeriesID, string MaterialProperty, string ModelID, string ShopList, int Page, int Size)
        {
            try
            {
                var sql = $@"SELECT  table2.* ,
        tre.ItemID ,
        tre.CompanyID ,
        tre.TotalSaleQty ,
        tre.TotalMoveQty ,
        tre.SaleRate ,
        tre.MoveDeepQty ,
        tre.CKMoveDeepQty ,
        tre.CheckState ,
        tre.Remark ,
        item.ItemName
FROM    ( SELECT    *
          FROM      ( SELECT    tre.Yearv ,
                                tre.Monthv ,
                                tre.ParentAreaID ,
                                tre.ShopID ,
                                tre.KindID ,
                                tre.MaterialProperty ,
                                tre.ModelID ,
                                tre.SeriesID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                dict.dictName AS ParentAreaName ,
                                kind.KindName ,
                                model.ModelName ,
                                series.SeriesName ,
                                ROW_NUMBER() OVER ( ORDER BY tre.Yearv DESC, tre.Monthv DESC, tre.ParentAreaID, tre.ShopID, tre.KindID, tre.MaterialProperty, tre.ModelID, tre.SeriesID ) AS Row
                      FROM  ngoods.ngoods_PL_ShopMSaleTrend tre 
left join ndw.ndw_Shop shop on tre.ShopID=shop.ShopID
   and  tre.ExtrSource = shop.ExtrSource and tre.CompanyID=shop.CompanyID 
left join ndw.ndw_global_dictionary dict on tre.ParentAreaID=dict.dictValue
   and  tre.ExtrSource = dict.ExtrSource and tre.CompanyID=dict.CompanyID 
left join nods.nods_erp_SD_Mat_Kind kind on tre.KindID=kind.KindID   and  tre.ExtrSource = kind.ExtrSource 
left join nods.nods_erp_SD_Mat_Item item on tre.ItemID=item.ItemID  and  tre.ExtrSource = item.ExtrSource 
left join nods.nods_erp_SD_Mat_Model model on tre.ModelID=model.ModelID  and  tre.ExtrSource = model.ExtrSource 
left join nods.nods_erp_SD_Mat_Series series on tre.SeriesID=series.SeriesID  and  tre.ExtrSource = series.ExtrSource 
where 
tre.ExtrSource = '{UserInfo.UserExtrSource}' and tre.CompanyID='{UserInfo.UserCompanyId}' and tre.Yearv={Yearv} and tre.Monthv={Monthv} 
		                        {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and tre.ParentAreaID='{ParentAreaID}' ")}
		                        {(string.IsNullOrEmpty(KindID) ? "" : $@" and tre.KindID='{KindID}' ")}
		                        {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and tre.SeriesID='{SeriesID}' ")}
		                        {(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and tre.MaterialProperty='{MaterialProperty}' ")}
		                        {(string.IsNullOrEmpty(ModelID) ? "" : $@" and tre.ModelID='{ModelID}' ")}
		                        {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                      GROUP BY  tre.Yearv ,
                                tre.Monthv ,
                                tre.ParentAreaID ,
                                tre.ShopID ,
                                tre.KindID ,
                                tre.MaterialProperty ,
                                tre.ModelID ,
                                tre.SeriesID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                dict.dictName ,
                                kind.KindName ,
                                model.ModelName ,
                                series.SeriesName
                    ) table1 where Row BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size}
        ) table2
        LEFT JOIN ( SELECT  tre.*
                    FROM    ngoods.ngoods_PL_ShopMSaleTrend tre
                            LEFT JOIN ndw.ndw_Shop shop ON tre.ShopID = shop.ShopID
   and  tre.ExtrSource = shop.ExtrSource and tre.CompanyID=shop.CompanyID 
                    where 
tre.ExtrSource = '{UserInfo.UserExtrSource}' and tre.CompanyID='{UserInfo.UserCompanyId}' and 
tre.Yearv={Yearv} and tre.Monthv={Monthv} 
		            {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and tre.ParentAreaID='{ParentAreaID}' ")}
		            {(string.IsNullOrEmpty(KindID) ? "" : $@" and tre.KindID='{KindID}' ")}
		            {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and tre.SeriesID='{SeriesID}' ")}
		            {(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and tre.MaterialProperty='{MaterialProperty}' ")}
		            {(string.IsNullOrEmpty(ModelID) ? "" : $@" and tre.ModelID='{ModelID}' ")}
		            {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                  ) tre ON table2.Yearv = tre.Yearv
                           AND table2.Monthv = tre.Monthv
                           AND table2.ParentAreaID = tre.ParentAreaID
                           AND table2.ShopID = tre.ShopID
                           AND table2.KindID = tre.KindID
                           AND table2.MaterialProperty = tre.MaterialProperty
                           AND table2.ModelID = tre.ModelID
                           AND table2.SeriesID = tre.SeriesID
        LEFT JOIN nods.nods_erp_SD_Mat_Item item ON tre.ItemID = item.ItemID and item.ExtrSource = '{UserInfo.UserExtrSource}'
ORDER BY table2.Yearv DESC ,
        table2.Monthv DESC ,
        table2.ParentAreaID ,
        table2.ShopID ,
        table2.KindID ,
        table2.MaterialProperty ,
        table2.ModelID ,
        table2.SeriesID ,
        tre.ItemID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回小品类深度总条数
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="Keys"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-04-01
        public int ReturnPLShopMSaleTrendCount(long Yearv, long Monthv, string ParentAreaID, string KindID, string SeriesID, string MaterialProperty, string ModelID, string ShopList)
        {
            try
            {
                var sql =
                    $@"select count(1) from(
select count(1) from ngoods.ngoods_PL_ShopMSaleTrend tre 
left join ndw.ndw_Shop shop on tre.ShopID=shop.ShopID
   and  tre.ExtrSource = shop.ExtrSource and tre.CompanyID=shop.CompanyID 
left join ndw.ndw_global_dictionary dict on tre.ParentAreaID=dict.dictValue
   and  tre.ExtrSource = dict.ExtrSource and tre.CompanyID=dict.CompanyID 
left join nods.nods_erp_SD_Mat_Kind kind on tre.KindID=kind.KindID   and  tre.ExtrSource = kind.ExtrSource 
left join nods.nods_erp_SD_Mat_Item item on tre.ItemID=item.ItemID  and  tre.ExtrSource = item.ExtrSource 
left join nods.nods_erp_SD_Mat_Model model on tre.ModelID=model.ModelID  and  tre.ExtrSource = model.ExtrSource 
left join nods.nods_erp_SD_Mat_Series series on tre.SeriesID=series.SeriesID  and  tre.ExtrSource = series.ExtrSource 
where 
tre.ExtrSource = '{UserInfo.UserExtrSource}' and tre.CompanyID='{UserInfo.UserCompanyId}' and
tre.Yearv={Yearv} and tre.Monthv={Monthv} 
{(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and tre.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrEmpty(KindID) ? "" : $@" and tre.KindID='{KindID}' ")}
{(string.IsNullOrEmpty(SeriesID) ? "" : $@" and tre.SeriesID='{SeriesID}' ")}
{(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and tre.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrEmpty(ModelID) ? "" : $@" and tre.ModelID='{ModelID}' ")}
{(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
group by tre.Yearv,tre.Monthv,tre.ParentAreaID,tre.ShopID,tre.KindID,tre.MaterialProperty,tre.ModelID,tre.SeriesID
)table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-04-01
        [JsonType]
        [MustLogin]
        [Description("修改小品类深度")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLShopMSaleTrendModel.ListModel>>))]
        public ApiResult UpdatePLShopMSaleTrend([ParamDescription("商品区域")] PLShopMSaleTrendModel.ModifyModel param)
        {
            try
            {
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var uid = Guid.NewGuid().ToString().Replace("-", "");
                    var tempTableName = $" ngoods.ngoods_{uid} ";
                    const string targetTableName = @" ngoods.ngoods_PL_ShopMSaleTrend ";
                    var dt = DataTableHelper.LinqToDataTable(param.list);
                    const string onConditions = @" targetTable.CompanyID=sourceTable.CompanyID
         AND targetTable.ExtrSource=sourceTable.ExtrSource
         AND targetTable.Yearv=sourceTable.Yearv
         AND targetTable.Monthv=sourceTable.Monthv
         AND targetTable.ParentAreaID=sourceTable.ParentAreaID
         AND targetTable.ShopID=sourceTable.ShopID
         AND targetTable.KindID=sourceTable.KindID
         AND targetTable.SeriesID=sourceTable.SeriesID
         AND targetTable.MaterialProperty=sourceTable.MaterialProperty
         AND targetTable.ModelID=sourceTable.ModelID
         AND targetTable.ItemID=sourceTable.ItemID ";
                    const string setConditions = @" CKMoveDeepQty = sourceTable.CKMoveDeepQty,
                                           CheckState = 1 ";
                    Helper.Merge(tempTableName, targetTableName, dt, onConditions, setConditions, null,
                        null, false);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

    }
}
